﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Resources;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;
using Application = Microsoft.Office.Interop.Excel.Application;

namespace Workflows.Components.Entities.Collapses
{
    /// <summary>
    /// 
    /// </summary>
    public class WorksheetHelper
    {
        #region range
        /// <summary></summary>
        public static Range GetCell(Worksheet sheet, int colIndex, int rowIndex)
        {
            return (Range)sheet.Cells[rowIndex, colIndex];
        }
        /// <summary></summary>
        public static Range GetRange(Worksheet sheet, string name)
        {
            return WorksheetHelper.GetCell(sheet, WorksheetHelper.GetColumnIndex(name), WorksheetHelper.GetRowIndex(name));
        }
        /// <summary></summary>
        public static int GetColumnIndex(string name)
        {
            if (name.IndexOf(":") > 0)
                name = name.Substring(0, name.IndexOf(":"));
            Regex charRegex = new Regex("(^[^\\d]+)\\d+$");
            Match charMatch = charRegex.Match(name);
            Char[] colChars = null;
            if (charMatch.Success)
            {
                colChars = charMatch.Groups[1].Value.ToCharArray();
            }
            else
            {
                colChars = name.ToCharArray();
            }

            int colIndex = 0;
            if (colChars.Length == 1)
            {
                return WorksheetHelper.Asc(colChars[0]) - WorksheetHelper.Asc('A') + 1;
            }
            else
            {
                int digit1 = (WorksheetHelper.Asc(colChars[0]) - WorksheetHelper.Asc('A') + 1) * 26;
                int digit2 = WorksheetHelper.Asc(colChars[1]) - WorksheetHelper.Asc('A') + 1;
                return digit1 + digit2;
            }
        }
        /// <summary></summary>
        public static string GetColumnName(int colIndex)
        {
            char left = ' ';
            char right = ' ';
            for (int i = 0; i < 26; i++)
            {
                for (int k = 0; k <= 26; k++)
                {
                    int index = i * 26 + k;
                    if (index == colIndex)
                    {
                        if (i > 0)
                        {
                            left = WorksheetHelper.Chr(64 + i);
                        }
                        right = WorksheetHelper.Chr(64 + k);

                        k = 100;
                        i = 100;
                        break;
                    }
                }
            }
            return string.Concat(left.ToString(), right.ToString()).Trim();
        }
        /// <summary></summary>
        public static int GetRowIndex(string name)
        {
            Regex charRegex = new Regex("(^[^\\d]+)(\\d+)$");
            Match charMatch = charRegex.Match(name);
            int rowIndex = int.Parse(charMatch.Groups[2].Value);
            return rowIndex;
        }
        /// <summary></summary>
        public static int Asc(char c)
        {
            return (int)Encoding.ASCII.GetBytes(new char[] { c })[0];
        }
        /// <summary></summary>
        public static char Chr(int i)
        {
            return Convert.ToChar(i);
        }
        /// <summary></summary>
        public static void SetValue(Worksheet sheet, string name, object value)
        {
            Range range = WorksheetHelper.GetRange(sheet, name);
            range.Value2 = value;
        }
        /// <summary></summary>
        public static string GetEmbeddedResourceFileContent(string resourceName)
        {
            ResourceManager resMgr =
                new ResourceManager(typeof(WorksheetHelper).Namespace + ".Resource1", Assembly.GetAssembly(typeof(WorksheetHelper)));

            return resMgr.GetString(resourceName);
        }
        #endregion

        #region fill table
        /// <summary></summary>
        public static void SetCountValue(Worksheet sheet, string rangeName, string sqlName, SqlCommand cmd, string[] replaceWithParameters)
        {
            WorksheetHelper.SetValue(sheet, rangeName, "...");
            string sql = WorksheetHelper.GetEmbeddedResourceFileContent(sqlName);
            if (replaceWithParameters != null && replaceWithParameters.Length > 0)
            {
                sql = string.Format(sql, replaceWithParameters);
            }
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            int value = int.Parse(cmd.ExecuteScalar().ToString());
            WorksheetHelper.SetValue(sheet, rangeName, value);
        }
        /// <summary></summary>
        public static void FillTable(Worksheet sheet, string rangeFrom, string rangeTo, DataTable dtData)
        {
            int colIndex1 = WorksheetHelper.GetColumnIndex(rangeFrom);
            int rowIndex1 = WorksheetHelper.GetRowIndex(rangeFrom);
            int colIndex2 = WorksheetHelper.GetColumnIndex(rangeTo);
            int rowIndex2 = WorksheetHelper.GetRowIndex(rangeFrom);
            int colCount = colIndex2 - colIndex1 + 1;
            int rowCount = rowIndex2 - rowIndex1 + 1;
            if (dtData == null || dtData.Rows.Count != rowCount || dtData.Columns.Count != colCount)
            {
                throw new Exception("Range and table size do not agree");
            }

            for (int row = rowIndex1; row <= rowIndex2; row++)
            {
                DataRow dr = dtData.Rows[row - rowIndex1];
                for (int col = colIndex1; col <= colIndex2; col++)
                {
                    Range range = WorksheetHelper.GetCell(sheet, col, row);
                    object value = dr[col - colIndex1];
                    range.Value2 = value;
                }
            }
        }
        /// <summary></summary>
        public static void FillTable(Worksheet sheet, string rangeFrom, Dictionary<string, int> cellColumnIndexByColNames, DataTable dtData)
        {
            int rowIndex1 = WorksheetHelper.GetRowIndex(rangeFrom);
            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                int rowIndex = rowIndex1 + i;
                for (int k = 0; k < dtData.Columns.Count; k++)
                {
                    string colName = dtData.Columns[k].ColumnName;
                    int colIndex = cellColumnIndexByColNames[colName];
                    Range cell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                    object value = dtData.Rows[i][k];
                    cell.Value2 = value;
                }
            }
        }
        /// <summary></summary>
        public static void FillTable(Worksheet sheet, string rangeFrom, Dictionary<string, int> cellColumnIndexByColNames, SqlDataReader reader, string keyColumnName)
        {
            int rowIndex1 = WorksheetHelper.GetRowIndex(rangeFrom);
            DataTable dtData = new DataTable();
            for (int k = 0; k < reader.FieldCount; k++)
            {
                string colName = reader.GetName(k);
                Type colDbType = reader.GetFieldType(k);
                dtData.Columns.Add(colName, colDbType);
            }
            while (reader.Read())
            {
                DataRow dr = dtData.NewRow();
                for (int i = 0; i < dtData.Columns.Count; i++)
                {
                    dr[i] = reader[i];
                }
                dtData.Rows.Add(dr);
            }

            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(keyColumnName))
                {
                    int rowIndex = 0;
                    object keyValue = dtData.Rows[i][keyColumnName];
                    int keyColumnIndex = cellColumnIndexByColNames[keyColumnName];
                    for (int rowIdx = rowIndex1; rowIdx < rowIndex1 + dtData.Rows.Count; rowIdx++)
                    {
                        Range cell = WorksheetHelper.GetCell(sheet, keyColumnIndex, rowIdx);
                        if (cell.Value2 != null && keyValue != null && cell.Value2.ToString() == keyValue.ToString())
                        {
                            rowIndex = rowIdx;
                            break;
                        }
                    }
                    if (rowIndex > 0)
                    {
                        for (int k = 0; k < dtData.Columns.Count; k++)
                        {
                            string colName = dtData.Columns[k].ColumnName;
                            if (colName != keyColumnName)
                            {
                                if (cellColumnIndexByColNames.ContainsKey(colName))
                                {
                                    int colIndex = cellColumnIndexByColNames[colName];
                                    Range cell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                                    object value = dtData.Rows[i][k];
                                    cell.Value2 = value;
                                }
                            }
                        }
                    }
                }
                else
                {
                    int rowIndex = rowIndex1 + i;
                    for (int k = 0; k < dtData.Columns.Count; k++)
                    {
                        string colName = dtData.Columns[k].ColumnName;
                        if (cellColumnIndexByColNames.ContainsKey(colName))
                        {
                            int colIndex = cellColumnIndexByColNames[colName];
                            Range cell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                            object value = dtData.Rows[i][k];
                            cell.Value2 = value;
                        }
                    }
                }
            }
        }
        /// <summary></summary>
        public static void FillTable(Worksheet sheet, string rangeFrom, Dictionary<string, int> cellColumnIndexByColNames, DataTable dtData, string keyColumnName)
        {
            int rowIndex1 = WorksheetHelper.GetRowIndex(rangeFrom);
            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(keyColumnName))
                {
                    int rowIndex = 0;
                    object keyValue = dtData.Rows[i][keyColumnName];
                    int keyColumnIndex = cellColumnIndexByColNames[keyColumnName];
                    for (int rowIdx = rowIndex1; rowIdx < rowIndex1 + dtData.Rows.Count; rowIdx++)
                    {
                        Range cell = WorksheetHelper.GetCell(sheet, keyColumnIndex, rowIdx);
                        if (cell.Value2 != null && keyValue != null && cell.Value2.ToString() == keyValue.ToString())
                        {
                            rowIndex = rowIdx;
                            break;
                        }
                    }
                    if (rowIndex > 0)
                    {
                        for (int k = 0; k < dtData.Columns.Count; k++)
                        {
                            string colName = dtData.Columns[k].ColumnName;
                            if (colName != keyColumnName)
                            {
                                if (cellColumnIndexByColNames.ContainsKey(colName))
                                {
                                    int colIndex = cellColumnIndexByColNames[colName];
                                    Range cell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                                    object value = dtData.Rows[i][k];
                                    cell.Value2 = value;
                                }
                            }
                        }
                    }
                }
                else
                {
                    int rowIndex = rowIndex1 + i;
                    for (int k = 0; k < dtData.Columns.Count; k++)
                    {
                        string colName = dtData.Columns[k].ColumnName;
                        if (cellColumnIndexByColNames.ContainsKey(colName))
                        {
                            int colIndex = cellColumnIndexByColNames[colName];
                            Range cell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                            object value = dtData.Rows[i][k];
                            cell.Value2 = value;
                        }
                    }
                }
            }
        }
        /// <summary></summary>
        public static void FillTable(Worksheet sheet, string rangeFrom, Dictionary<string, int> cellColumnIndexByColNames, string sqlName, SqlCommand cmd, string[] sqlParams, string keyColName)
        {
            string sql = WorksheetHelper.GetEmbeddedResourceFileContent(sqlName);
            if (sqlParams != null && sqlParams.Length > 0)
            {
                sql = string.Format(sql, sqlParams);
            }
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            SqlDataReader reader = cmd.ExecuteReader();
            WorksheetHelper.FillTable(sheet, rangeFrom, cellColumnIndexByColNames, reader, keyColName);
            reader.Close();
        }
        /// <summary></summary>
        public static void CallStoredProc(string spName, List<SqlParameter> parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Enumeration"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 60 * 30;       // 30 min timeout
                if (parameters != null && parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region format
        /// <summary></summary>
        public static void FormatColumn(Worksheet ws, int colIndex, string format)
        {
            ((Range)ws.Cells[1, colIndex]).EntireColumn.NumberFormat = format;
        }
        /// <summary></summary>
        public static void FormatColumnText(Worksheet ws, int colIndex)
        {
            ((Range)ws.Cells[1, colIndex]).EntireColumn.NumberFormat = "@";
        }
        #endregion

        #region column headers 
        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="worksheetIndex"></param>
        /// <param name="startingRowIndex">table body row starting index (excluding col header)</param>
        /// <returns></returns>
        public static Dictionary<string,int> GetColumnHeaders(
            string excelFilePath, int worksheetIndex, ref int startingRowIndex)
        {
            Dictionary<string,int> columnHeaders=new Dictionary<string, int>();
            Dictionary<string,int> colHeadersCopy=new Dictionary<string, int>();

            Application excelApp = new Application();
            excelApp.Visible = true;
            Workbook excelWorkbook =
                excelApp.Workbooks.Open(
                    excelFilePath, 0, false, 5, "", "", true,
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    "\t", false, false, 0, true, false, false);
            excelApp.DisplayAlerts = false;
            Worksheet sheet = (Worksheet)excelWorkbook.Worksheets[worksheetIndex];
            int rowIndex = 1;
            int colIndex = 1;

            while(colIndex <=255 && rowIndex <=255)
            {
                Range colHeaderCell = WorksheetHelper.GetCell(sheet, colIndex, rowIndex);
                if(colHeaderCell.Value2!=null && (!string.IsNullOrEmpty(colHeaderCell.Value2.ToString())))
                {
                    if(!columnHeaders.ContainsKey(colHeaderCell.Value2.ToString()))
                    {
                        columnHeaders.Add(colHeaderCell.Value2.ToString(), colIndex);
                    }
                    else
                    {
                        rowIndex++;
                        colIndex = 1;
                        columnHeaders.Clear();
                    }
                    colIndex++;
                }
                else
                {
                    if(columnHeaders.Count > colHeadersCopy.Count)
                    {
                        colHeadersCopy = new Dictionary<string, int>();
                        foreach(string key in columnHeaders.Keys)
                        {
                            colHeadersCopy.Add(key, columnHeaders[key]);
                        }
                        startingRowIndex = rowIndex + 1;
                    }
                    rowIndex++;
                    colIndex = 1;
                    columnHeaders.Clear();
                }
            }

            excelWorkbook.Close(false,excelFilePath,null);
            excelApp.Quit();

            return colHeadersCopy;
        }
        #endregion
    }
}
